﻿import java.sql.Connection
import java.sql.Statement;

class SqlService {
    javax.sql.DataSource dataSource;
    boolean transactional = true

    def List executeQueryList(String sqlString, String fieldNamesCSV) {
        java.sql.ResultSet rs = null;
        def data = new ArrayList();
        Connection conn = null;
        Statement st = null;

        try {
            conn = dataSource.getConnection();
            st = conn.createStatement();
            rs = st.executeQuery(sqlString);
            def columnCount = rs.getMetaData().getColumnCount();

            String[] fieldNames = fieldNamesCSV.split(",");
            while (rs.next()) {
                def row = new ArrayList();
                fieldNames.each {
                    String field = it.toString();
                    if (StringUtils.isNotEmpty(field))
                        row.add(rs.getString(field.trim()))
                }
                data.add(row);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (rs != null)
                rs.close();
            if (st != null)
                st.close();
            if (conn != null)
                conn.close();
        }
        return data;
    }
    def List executeQueryMap(String sqlString) {
        java.sql.ResultSet rs = null;
        def data = new ArrayList();
        Connection conn = null;
        Statement st = null;
        try {
            conn = dataSource.getConnection();
            st = conn.createStatement();
            rs = st.executeQuery(sqlString);
            def columnCount = rs.getMetaData().getColumnCount();

            while (rs.next()) {
                def map = new HashMap();
                for (i in 0..columnCount) {
                    try{
                    String field = rs.getMetaData().getColumnLabel(i);
                    if (StringUtils.isNotEmpty(field)) {

                        map.put(field.trim(), rs.getObject(field.trim()))
                    }
                    }catch(Exception e){}
                }
                data.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (rs != null)
                rs.close();
            if (st != null)
                st.close();
            if (conn != null)
                conn.close();
        }
        return data;
    }
    def boolean execute(String sqlString) {
        Connection conn = null;
        Statement st = null;
        try {
            conn = dataSource.getConnection();
            st = conn.createStatement();
            return st.execute(sqlString);
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (st != null)
                st.close();
            if (conn != null)
                conn.close();
        }
    }

    /*def Object executeQuery(String sqlString, Class c){

        //def map = obj.getProperties();
        //MetaPropertieValue
        java.sql.ResultSet rs = dataSource.getConnection().createStatement().executeQuery(sqlString);
        def data = new ArrayList();
        while(rs.next()){
            Object obj = c.newInstance();
            c.fields.each {
                    String field = it.name;
                    try{

                        //System.out.println(field);
                        if(!field.startsWith("__"))
                            obj[field] = rs.getString(field.trim());
                    }catch(Exception e){
                        //System.out.println("+++++++++++"+field);
                    }
                    //System.out.printn(field);
                    System.out.println(((State)obj)[field]);
            }
            data.add(obj);
        }
	}*/
}

